CREATE proc [dbo].[asi_NextSequenceValueSub]
  @sequenceName nvarchar(30),
  @userKey uniqueidentifier,
  @systemEntityKey uniqueidentifier = '00000000-0000-0000-0000-000000000000',
  @increment int = 1,
  @startValue int = 0,
  @lastValue int = 0 output as

--Check to make sure the counter exists - create it if not
if not exists(select 1 from SequenceCounter where CounterName = @sequenceName and SystemEntityKey = @systemEntityKey)
   insert SequenceCounter (SystemEntityKey, CounterName, CurrentValue, UpdatedOn, UpdatedByUserKey)
   values (@systemEntityKey, @sequenceName, @startValue, getdate(), @userKey)

--Increment the current value
update SequenceCounter
   set @lastValue = CurrentValue = (CurrentValue + @increment),
       UpdatedOn = getdate(),
       UpdatedByUserKey = @userKey
where CounterName = @sequenceName
   and SystemEntityKey = @systemEntityKey

GO